CREATE PROCEDURE [dbo].[asi_HierarchyResortChildren]
    @parentHierarchyKey uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO #Reorder (HierarchyKey) VALUES (@parentHierarchyKey)

    DECLARE @ChildrenTable TABLE
    (
        HierarchyKey uniqueidentifier,
        SortOrder int
    )

    INSERT INTO @ChildrenTable
    SELECT HierarchyKey, SortOrder FROM Hierarchy WHERE ParentHierarchyKey = @parentHierarchyKey

    DECLARE @childHierarchyKey uniqueidentifier
    
    WHILE EXISTS (SELECT 1 FROM @ChildrenTable)
    BEGIN
        SELECT TOP 1 @childHierarchyKey = HierarchyKey FROM @ChildrenTable ORDER BY SortOrder
        EXEC asi_HierarchyResortChildren @childHierarchyKey
        DELETE FROM @ChildrenTable WHERE HierarchyKey = @childHierarchyKey
    END

    SET NOCOUNT OFF
END

GO